/**
 * Copyright (c) 2005-2016, yinwer81 (yinwer81@aliyun.com)
 *
 * Licensed under the Apache License, Version 2.0
 */
package com.nozturn.throne.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;

import com.nozturn.throne.dao.AccessTokenDao;
import com.nozturn.throne.entity.AccessToken;
import com.nozturn.throne.entity.OAuthInfoDTO;

@Repository
public class AccessTokenDaoImpl implements AccessTokenDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public AccessToken createAccessToken(final AccessToken accessToken) {
        final String sql = "insert into tbl_access_token(access_token, authorization_pk, expires_in, valid, created_at, updated_at, created_by, updated_by) values(?,?,?,?,now(),now(),?,?)";

        final GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement psst = connection.prepareStatement(sql, new String[] { "pk" });
                int count = 1;
                psst.setString(count++, accessToken.getAccessToken());
                psst.setLong(count++, accessToken.getAuthorizationPk());
                psst.setInt(count++, accessToken.getExpiresIn());
                psst.setInt(count++, accessToken.getValid());
                psst.setString(count++, accessToken.getCreatedBy());
                psst.setString(count++, accessToken.getUpdatedBy());
                return psst;
            }
        }, keyHolder);

        accessToken.setPk(keyHolder.getKey().longValue());
        return accessToken;
    }

    public int updateAccessToken(final AccessToken accessToken) {
        final String sql = "update tbl_access_token set valid=?, updated_at=now(), updated_by=? where valid=200 and pk=?";
        int updated = jdbcTemplate.update(sql, accessToken.getValid(), accessToken.getUpdatedBy(), accessToken.getPk());
        return updated;
    }

    public int deleteAccessToken(final String accessToken) {
        final String sql = "update tbl_access_token set valid=100, updated_at=now(), updated_by='system' where valid=200 and access_token=?";
        int updated = jdbcTemplate.update(sql, accessToken);
        return updated;
    }

    private int deleteAccessToken(final Long pk) {
        final String sql = "update tbl_access_token set valid=100, updated_at=now(), updated_by='system' where valid=200 and pk=?";
        int updated = jdbcTemplate.update(sql, pk);
        return updated;
    }

    public AccessToken findOne(final Long pk) {
        final String sql = "select pk, access_token, authorization_pk, created_at, expires_in, valid,created_at, updated_at, created_by, updated_by from tbl_access_token where valid=200 and pk = ? and (now() between created_at and date_add(created_at, interval expires_in second))";
        final List<AccessToken> accessTokenList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<AccessToken>(AccessToken.class), pk);
        if (accessTokenList.size() == 0) {
            deleteAccessToken(pk);
            return null;
        }
        return accessTokenList.get(0);
    }

    public AccessToken findByAccessToken(final String accessToken) {
        final String sql = "select pk, access_token, authorization_pk, created_at, expires_in, valid,created_at, updated_at, created_by, updated_by from tbl_access_token where valid=200 and access_token = ? and (now() between created_at and date_add(created_at, interval expires_in second))";
        final List<AccessToken> accessTokenList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<AccessToken>(AccessToken.class), accessToken);
        if (accessTokenList.size() == 0) {
            deleteAccessToken(accessToken);
            return null;
        }
        return accessTokenList.get(0);
    }

    public OAuthInfoDTO findAllByAccessToken(final String accessToken) {
        final String sql = "select tat.pk access_token_pk, tat.access_token, tat.authorization_pk, tat.created_at, tat.expires_in, tat.valid access_token_valid , "
                + "ta.pk authorization_pk, ta.authorization_code, ta.valid authorization_valid, tu.pk user_pk, tu.username, tu.usertype, "
                + "tu.valid user_valid, tc.pk client_pk, tc.client_name, tc.client_id,tc.client_secret, tc.valid client_valid from "
                + "tbl_access_token tat left join tbl_authorization ta on tat.authorization_pk = ta.pk left join tbl_user tu on"
                + " ta.user_pk = tu.pk left join tbl_client tc on ta.client_pk = tc.pk where tat.access_token = ?";
        final List<OAuthInfoDTO> list = jdbcTemplate.query(sql,
                new BeanPropertyRowMapper<OAuthInfoDTO>(OAuthInfoDTO.class), accessToken);
        if (list.size() == 0) {
            return null;
        }
        return list.get(0);
    }

}
